﻿using System;
using System.Collections.Generic;
using System.Text;
using System.Data;
using System.Data.SqlClient;

namespace ASBCC.Database.Service
{
    public class CommonService
    {
        #region 角色
        /// <summary>
        /// 获取角色类型
        /// </summary>
        /// <returns></returns>
        public static DataTable GetRoleCategory(string key)
        {
            string sql = "select b.* from  SYS_PARAMETER_KEY a  inner join  SYS_PARAMETER_DETAIL  b" +
                         " on a.PARAMETER_KEY_ID=b.PARAMETER_KEY_ID where  a.PARAMETER_KEY='{0}'";
            sql = string.Format(sql, key);
            DataSet ds = SqlDataHelper.ExecuteDataset(
                DatabaseManager.ConnectionString,
                CommandType.Text,
                sql);
            if (ds == null || ds.Tables.Count == 0) return null;
            return ds.Tables[0];
        }

        /// <summary>
        /// 获取指定角色的菜单
        /// </summary>
        /// <param name="condtion"></param>
        /// <returns></returns>
        public static DataTable GetRoleMenuInfo(string condtion)
        {
            string sql = "select distinct sys_menu.* from   RoleMenu  inner join sys_menu on "
                          +" rolemenu.fmenu_id=sys_menu.menu_id where  1=1 {0} ";
 
            sql = string.Format(sql, condtion);
            DataSet ds = SqlDataHelper.ExecuteDataset(
                DatabaseManager.ConnectionString,
                CommandType.Text,
                sql);
            if (ds == null || ds.Tables.Count == 0) return null;
            return ds.Tables[0];
        }

        /// <summary>
        /// 获取角色未关联的operationInfo
        /// </summary>
        /// <param name="roleid"></param>
        /// <returns></returns>
        public static DataTable GetRoleNonOperationInfo(string roleid)
        {
            string sql = string.Format("select  [OI_ID] ,[OI_Number] ,[OI_TYPE] , '项目管理-'+[OI_Name] as OI_Name,[OI_Description] ,[FI_ID] from sys_operationInfo where   " +
                            " oi_id not in (select distinct or_oi_id from Sys_operationRule where or_rule_id={0}   ) order by  OI_Name asc ", roleid);
            DataSet ds = SqlDataHelper.ExecuteDataset(
                DatabaseManager.ConnectionString,
                CommandType.Text,
                sql);
            if (ds == null || ds.Tables.Count == 0) return null;
            return ds.Tables[0];
        }

        /// <summary>
        /// 获取角色关联的operationInfo
        /// </summary>
        /// <param name="roleid"></param>
        /// <returns></returns>
        public static DataTable GetRoleOperationInfo(String roleid)
        {
            string sql = string.Format("select  [OI_ID] ,[OI_Number] ,[OI_TYPE] , '项目管理-'+[OI_Name] as OI_Name,[OI_Description] ,[FI_ID]    " +
                        " from SYS_OperationRule inner join SYS_OperationInfo on SYS_OperationRule.or_oi_id=SYS_OperationInfo.oi_id where or_rule_id= {0} order by OI_Name asc ", roleid);
            DataSet ds = SqlDataHelper.ExecuteDataset(
                DatabaseManager.ConnectionString,
                CommandType.Text,
                sql);
            if (ds == null || ds.Tables.Count == 0) return null;
            return ds.Tables[0];
        }


        /// <summary>
        /// 获取角色未关联的FunctionInfo
        /// </summary>
        /// <param name="roleid"></param>
        /// <returns></returns>
        public static DataTable GetRoleNonFunctionInfo(string roleid)
        {
            string sql = string.Format("select   * from sys_functionInfo where     " +
                         " fi_id not in (select distinct fr_fi_id from sys_functionrule where fr_rule_id={0} ) order by FI_Name asc ", roleid);
            DataSet ds = SqlDataHelper.ExecuteDataset(
                DatabaseManager.ConnectionString,
                CommandType.Text,
                sql);
            if (ds == null || ds.Tables.Count == 0) return null;
            return ds.Tables[0];
        }

        /// <summary>
        /// 获取角色关联的FunctionInfo
        /// </summary>
        /// <param name="roleid"></param>
        /// <returns></returns>
        public static DataTable GetRoleFunctionInfo(string roleid)
        {
            string sql = string.Format("select  distinct  [FI_ID],[FI_Number],[FI_Name],[FI_FullPath],[FI_Description]     " +
                         " from SYS_FunctionRule inner join SYS_FunctionInfo on SYS_FunctionRule.fr_fi_id=SYS_FunctionInfo.fi_id where fr_rule_id={0} order by FI_Name asc", roleid);
            DataSet ds = SqlDataHelper.ExecuteDataset(
                DatabaseManager.ConnectionString,
                CommandType.Text,
                sql);
            if (ds == null || ds.Tables.Count == 0) return null;
            return ds.Tables[0];
        }

        #endregion

        /// <summary>
        /// 获得用户列表
        /// </summary>
        /// <param name="condition"></param>
        /// <returns></returns>
        public static DataTable GetUserList(string condition)
        {
            string sql = string.Format("select * from V_User_Info where 1=1 {0}", condition);
            DataSet ds = SqlDataHelper.ExecuteDataset(
                DatabaseManager.ConnectionString,
                CommandType.Text,
                sql);
            if (ds == null || ds.Tables.Count == 0) return null;
            return ds.Tables[0];
        }

        /// <summary>
        /// 获得用户代理列表
        /// </summary>
        /// <param name="condition"></param>
        /// <returns></returns>
        public static DataTable GetUserDelegationList(int userId)
        {
            string sql =
@"select ud.UserDelegationID,c.CategoryName,c.CategoryDisplayEn,c.CategoryDisplayCn,ui.User_ID DelegateToID,ui.English_Name DelegateToEn,ui.User_Name DelegateToCn,ud.FromDate,ud.ToDate,ud.Disabled
from Flow_Category c 
left join UserDelegation ud on c.CategoryName=ud.CategoryName and ud.UserID=@UserID
left join User_Info ui on ud.DelegateTo=ui.User_ID
where c.DelegationRequired=1";

            SqlParameter[] parameters = {
                    new SqlParameter("@UserID", SqlDbType.Int)};
            parameters[0].Value = userId;

            DataSet ds = SqlDataHelper.ExecuteDataset(
                DatabaseManager.ConnectionString,
                CommandType.Text,
                sql,
                parameters);
            if (ds == null || ds.Tables.Count == 0) return null;
            return ds.Tables[0];
        }
        /// <summary>
        /// 获取所有的菜单
        /// </summary>
        /// <returns></returns>
        public static  DataSet GetAllMenu()
        {
            string sql = string.Format("select * from sys_menu");
            return SqlDataHelper.ExecuteDataset(
                DatabaseManager.ConnectionString,
                CommandType.Text,
                sql);
        }

        /// <summary>
        /// 获取所有的function
        /// </summary>
        /// <returns></returns>
        public static DataSet GetAllFunction()
        { 
            string sql = string.Format("select distinct * from sys_functionInfo");
            return SqlDataHelper.ExecuteDataset(
                DatabaseManager.ConnectionString,
                CommandType.Text,
                sql);
        }

        /// <summary>
        /// 获取所有的OperationInfo
        /// </summary>
        /// <returns></returns>
        public static DataSet GetAllOperation()
        {
            string sql = string.Format("select distinct * from sys_operationInfo");
            return SqlDataHelper.ExecuteDataset(
                DatabaseManager.ConnectionString,
                CommandType.Text,
                sql);
        }

        /// <summary>
        /// 获取所有的role
        /// </summary>
        /// <returns></returns>
        public static DataSet GetAllRole()
        {
            string sql = string.Format("select distinct * from [Role]");
            return SqlDataHelper.ExecuteDataset(
                DatabaseManager.ConnectionString,
                CommandType.Text,
                sql);
        }
    }
}
